﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Drawing;
using System.Data.OleDb;
using System.Data;

namespace FillReportOnline
{
    public partial class report : System.Web.UI.Page
    {
        static string connection = System.Configuration.ConfigurationManager.ConnectionStrings["datasource"].ToString();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (IsPostBack)
            {
                return;
            }

            if (this.Request.UrlReferrer != null)
            {
                if (this.Request.UrlReferrer.AbsolutePath == "/adddata.aspx")
                {
                    Session["report"] = "list";
                }
            }

            if (Session["report"] == null)
            {
                Session["report"] = "year";
            }

            LoadReportTem();
            SetSkin();
            this.FpSpread1.CommandBar.Visible = true;
        }

        private void InitListReport()
        {
            this.FpSpread1.SaveExcel(this.Server.MapPath(@"resource\template\report.xls"));

            OleDbConnection dbConnection = new OleDbConnection(connection);
            if (dbConnection == null)
                return;

            DataTable dbReport = new DataTable();

            string selectStr = "SELECT * from SalesData order by  ID desc";
            OleDbDataAdapter dbAdapter = new OleDbDataAdapter(selectStr, dbConnection);
            dbAdapter.Fill(dbReport);
            dbConnection.Close();

            this.FpSpread1.Sheets[0].DataAutoCellTypes = false;

            int rowCount = dbReport.Rows.Count;

            this.FpSpread1.Sheets[0].DataSource = dbReport;
            this.FpSpread1.Sheets[0].AllowLoadOnDemand = true;
            this.FpSpread1.Sheets[0].PageSize = rowCount;
            this.FpSpread1.Sheets[0].RowCount = rowCount;
            this.FpSpread1.Sheets[0].ColumnCount += 2;

            this.FpSpread1.Sheets[0].Columns[9].Label = "删除";
            FarPoint.Web.Spread.TextCellType butDel = new FarPoint.Web.Spread.TextCellType();
            butDel.CssClass = "cellClass";
            this.FpSpread1.Sheets[0].Columns[8].Label = "编辑";
            FarPoint.Web.Spread.TextCellType butEdit = new FarPoint.Web.Spread.TextCellType();

            butEdit.CssClass = "cellClass";

            FarPoint.Web.Spread.DateTimeCellType dc = new FarPoint.Web.Spread.DateTimeCellType();
            dc.FormatString = "yyyy-MM-dd";
            for (int i = 0; i < rowCount; i++)
            {
                this.FpSpread1.Sheets[0].Cells[i, 9].Text = "删除";
                this.FpSpread1.Sheets[0].Cells[i, 8].Text = "双击单元格编辑";
                this.FpSpread1.Sheets[0].Cells[i, 1].CellType = dc;
            }
            this.FpSpread1.Sheets[0].Columns[9].CellType = butDel;
            this.FpSpread1.Sheets[0].Columns[8].CellType = butEdit;
            this.FpSpread1.Sheets[0].Columns[8].Width = 130;
            this.FpSpread1.Sheets[0].Columns[8].Locked = true;
            this.FpSpread1.Sheets[0].Columns[9].Locked = true;
            this.FpSpread1.Sheets[0].Columns[0].Locked = true;

            SetSkin();
        }

        private void SetCellType()
        {
            //日历单元格
            FarPoint.Web.Spread.DateTimeCellType dc = new FarPoint.Web.Spread.DateTimeCellType();
            dc.FormatString = "MM/dd/yyyy";
            FpSpread1.Sheets[0].Columns[1].CellType = dc;

            //数值单元格
            FarPoint.Web.Spread.Extender.NumericUpDownCellType n = new FarPoint.Web.Spread.Extender.NumericUpDownCellType();
            n.Maximum = 1000;
            n.Minimum = 10;
            n.Step = 5;
            n.Width = 130;
            n.ShowEditor = true;
            FpSpread1.ActiveSheetView.Cells[4, 3].CellType = n;

            //掩码单元格
            FarPoint.Web.Spread.Extender.MaskedEditCellType m = new FarPoint.Web.Spread.Extender.MaskedEditCellType();
            m.Editor.BackColor = System.Drawing.Color.Beige;
            m.Editor.BorderWidth = 1;
            m.ShowEditor = true;
            m.MaskType = AjaxControlToolkit.MaskedEditType.Number;
            m.Mask = "$999,999.99";
            m.DisplayMoney = AjaxControlToolkit.MaskedEditShowSymbol.Left;
            m.ClearMaskOnLostFocus = true;
            m.PromptCharacter = "#";
            m.InputDirection = AjaxControlToolkit.MaskedEditInputDirection.LeftToRight;
            FpSpread1.ActiveSheetView.Cells[4, 7].CellType = m;

            //自动完成单元格
            FarPoint.Web.Spread.Extender.AutoCompleteCellType ac = new FarPoint.Web.Spread.Extender.AutoCompleteCellType();
            ac.CompletionInterval = 1;
            ac.CompletionSetCount = 5;
            ac.DelimiterCharacters = ";, :";
            AjaxControlToolkit.TextBoxWatermarkExtender twe = new AjaxControlToolkit.TextBoxWatermarkExtender();
            twe.WatermarkText = "输入'朱'测试...";
            ac.Extenders.Add(twe);
            ac.FirstRowSelected = true;
            ac.ServicePath = "WebService1.asmx";
            ac.ServiceMethod = "GetAllNames";
            ac.MinimumPrefixLength = 1;
            ac.EnableCaching = true;
            ac.ShowEditor = true;
            FpSpread1.ActiveSheetView.Cells[6, 7].CellType = ac;
        }
        private void LoadReportTem()
        {
            if (Session["report"] == "year")
            {
                this.FpSpread1.Open(this.Server.MapPath(@"resource\template\sryear.xml"));
                this.DropDownList1.SelectedIndex = 0;
                InitYearReport();
            }
            else if (Session["report"] == "daily")
            {
                this.FpSpread1.Open(this.Server.MapPath(@"resource\template\dailyreport.xml"));
                this.DropDownList1.SelectedIndex = 1;
                InitDailyReport();
            }
            else if (Session["report"] == "list")
            {
                this.DropDownList1.SelectedIndex = 2;
                InitListReport();
            }
        }

        private void InitDailyReport()
        {
            this.FpSpread1.SaveExcel(this.Server.MapPath(@"resource\template\report.xls"));

            if (!CheckDailyDataField())
            {
                ClientScript.RegisterStartupScript(this.GetType(), "a", "<script>alert('模板字段与数据库字段不一致')</script>");
                this.FpSpread1.Open(this.Server.MapPath(@"resource\template\dailyreport.xml"));
                SetSkin();
            }

            OleDbConnection dbConnection = new OleDbConnection(connection);
            if (dbConnection == null)
                return;

            DataTable dbReport = new DataTable();

            try
            {
                string selectStr = string.Format("SELECT 日期,产品,数量,收入,区域,销售经理 from SalesData where 日期=#{0}# order by  日期 desc", DateTime.Now.Date.ToShortDateString());
                OleDbDataAdapter dbAdapter = new OleDbDataAdapter(selectStr, dbConnection);
                dbAdapter.Fill(dbReport);
            }
            catch (Exception ex)
            {

            }

            //向 spread 报表中写数据
            FarPoint.Web.Spread.SheetView report = this.FpSpread1.Sheets[0];

            if (dbReport == null || dbReport.Rows.Count == 0)
            {
                this.FpSpread1.Sheets[0].Rows.Add(4, 1);
                this.FpSpread1.Sheets[0].Rows[4].Border = new FarPoint.Web.Spread.Border(BorderStyle.Solid, Color.Black, 1);
                this.FpSpread1.Sheets[0].Cells[4, 0].Border = new FarPoint.Web.Spread.Border(BorderStyle.None, Color.Black, 1);
                this.FpSpread1.Sheets[0].AddSpanCell(4, 1, 1, 6);
                this.FpSpread1.Sheets[0].Cells[4, 1].ForeColor = Color.Red; ;
                this.FpSpread1.Sheets[0].Cells[4, 1].Text = "提示：今天没有订单信息,请到“添加订单页”录入订单。";
                return;
            }
            int columnCount = dbReport.Columns.Count;
            int rowCount = dbReport.Rows.Count;

            report.Rows.Add(4, rowCount);
            for (int i = 0; i < report.Rows.Count; i++)
            {
                report.Rows[i].BackColor = System.Drawing.Color.White;
            }
            report.PageSize = rowCount + 9;

            for (int row = 0; row < rowCount; row++)
            {
                FarPoint.Web.Spread.DateTimeCellType dttype = new FarPoint.Web.Spread.DateTimeCellType();
                dttype.FormatString = "yyyy-MM-dd";
                report.Cells[row + 4, 1].CellType = dttype;
                for (int col = 0; col < columnCount; col++)
                {
                    report.Cells[row + 4, col + 1].Text = dbReport.Rows[row][col].ToString();
                    report.Cells[row + 4, col + 1].Border = new FarPoint.Web.Spread.Border(BorderStyle.Solid, System.Drawing.Color.Black, 1);

                }
            }


            //设置统计数据
            if (dbConnection.State == ConnectionState.Closed)
            {
                dbConnection.Open();
            }
            OleDbCommand command = new OleDbCommand("", dbConnection);
            command.CommandText = string.Format("select SUM(收入) from SalesData where 日期=#{0}#", DateTime.Now.ToShortDateString());
            double tincome = (double)command.ExecuteScalar();
            command.CommandText = string.Format("select SUM(数量) from SalesData where 日期=#{0}#", DateTime.Now.ToShortDateString());
            double tcount = (double)command.ExecuteScalar();
            command.CommandText = string.Format("select SUM(收入) from SalesData where 日期=#{0}#", DateTime.Now.AddDays(-1).ToShortDateString());
            //double yincome = (double)command.ExecuteScalar();
            double yincome = 0;
            if (String.IsNullOrEmpty(command.ExecuteScalar().ToString()))
                yincome = 0;
            else
                yincome = (double)command.ExecuteScalar();
            command.CommandText = string.Format("select SUM(数量) from SalesData where 日期=#{0}#", DateTime.Now.AddDays(-1).ToShortDateString());
            //double ycount = (double)command.ExecuteScalar();
            double ycount = 0;
            if (String.IsNullOrEmpty(command.ExecuteScalar().ToString()))
                ycount = 0;
            else
                ycount = (double)command.ExecuteScalar();

            dbConnection.Close();

            int formularow = this.FpSpread1.Sheets[0].RowCount - 2;
            this.FpSpread1.Sheets[0].Cells[formularow, 2].Value = tcount;
            this.FpSpread1.Sheets[0].Cells[formularow, 5].Value = tincome;
            this.FpSpread1.Sheets[0].Cells[formularow - 1, 2].Value = ycount;
            this.FpSpread1.Sheets[0].Cells[formularow - 1, 5].Value = yincome;

        }

        private bool CheckDailyDataField()
        {
            bool flag = true;
            FarPoint.Web.Spread.SheetView reportinfo = this.FpSpread1.Sheets[0];
            try
            {
                if (reportinfo.Cells[3, 1].Text.Replace(" ", "") != "日期")
                {
                    return false;
                }
                if (reportinfo.Cells[3, 2].Text.Replace(" ", "") != "产品")
                {
                    return false;
                }
                if (reportinfo.Cells[3, 3].Text.Replace(" ", "") != "数量(个)")
                {
                    return false;
                }
                if (reportinfo.Cells[3, 4].Text.Replace(" ", "") != "收入(￥)")
                {
                    return false;
                }
                if (reportinfo.Cells[3, 5].Text.Replace(" ", "") != "区域")
                {
                    return false;
                }
                if (reportinfo.Cells[3, 6].Text.Replace(" ", "") != "销售经理")
                {
                    return false;
                }
            }
            catch (Exception)
            {
                return false;
            }
            return flag;
        }

        private bool identify()
        {
            if (Session["report"] == "year")
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        #region 初始化报表
        private void InitYearReport()
        {
            this.FpSpread1.SaveExcel(this.Server.MapPath(@"resource\template\report.xls"));

            if (!CheckYearDataField())
            {
                ClientScript.RegisterStartupScript(this.GetType(), "a", "<script>alert('模板字段与数据库字段不一致')</script>");
                this.FpSpread1.Open(this.Server.MapPath(@"resource\template\sryear.xml"));
                SetSkin();
            }

            #region 取数据
            OleDbConnection dbConnection = new OleDbConnection(connection);
            if (dbConnection == null)
                return;
            if (dbConnection.State == ConnectionState.Closed)
            {
                dbConnection.Open();
            }

            DataTable dt = new DataTable();
            dt.Columns.Add("pro");
            dt.Columns.Add("count", typeof(System.Int32));
            dt.Columns.Add("income", typeof(System.Double));

            //按年分配
            OleDbCommand command = new OleDbCommand("", dbConnection);

            //ComponetOne
            command.CommandText = "select SUM(收入) from SalesData where 产品='ComponentOne Studio' and 日期 between #2012-01-01# and #2013-01-01#";
            double c1income = (double)command.ExecuteScalar();
            command.CommandText = "select SUM(数量) from SalesData where 产品='ComponentOne Studio' and 日期 between #2012-01-01# and #2013-01-01#";
            double c1count = (double)command.ExecuteScalar();
            dt.Rows.Add("ComponentOne Studio", c1count, c1income);

            //Spread
            command.CommandText = "select SUM(收入) from SalesData where 产品='Spread' and 日期 between #2012-01-01# and #2013-01-01#";
            double spincome = (double)command.ExecuteScalar();
            command.CommandText = "select SUM(数量) from SalesData where 产品='Spread' and 日期 between #2012-01-01# and #2013-01-01#";
            double spcount = (double)command.ExecuteScalar();
            dt.Rows.Add("Spread", spcount, spincome);

            //MultiRow
            command.CommandText = "select SUM(收入) from SalesData where 产品='MultiRow' and 日期 between #2012-01-01# and #2013-01-01#";
            double mtincome = (double)command.ExecuteScalar();
            command.CommandText = "select SUM(数量) from SalesData where 产品='MultiRow' and 日期 between #2012-01-01# and #2013-01-01#";
            double mtcount = (double)command.ExecuteScalar();
            dt.Rows.Add("MultiRow", mtcount, mtincome);

            //AR
            command.CommandText = "select SUM(收入) from SalesData where 产品='ActiveReports' and 日期 between #2012-01-01# and #2013-01-01#";
            double arincome = (double)command.ExecuteScalar();
            command.CommandText = "select SUM(数量) from SalesData where 产品='ActiveReports' and 日期 between #2012-01-01# and #2013-01-01#";
            double arcount = (double)command.ExecuteScalar();
            dt.Rows.Add("ActiveReports", arcount, arincome);

            //AR
            command.CommandText = "select SUM(收入) from SalesData where 产品='ActiveAnalysis' and 日期 between #2012-01-01# and #2013-01-01#";
            double aaincome = (double)command.ExecuteScalar();
            command.CommandText = "select SUM(数量) from SalesData where 产品='ActiveAnalysis' and 日期 between #2012-01-01# and #2013-01-01#";
            double aacount = (double)command.ExecuteScalar();
            dt.Rows.Add("ActiveAnalysis", aacount, aaincome);
            #endregion

            this.FpSpread1.Sheets[0].Cells[2, 4].Text = "统计时间：" + DateTime.Now.ToShortDateString();
            for (int i = 4; i < 9; i++)
            {
                this.FpSpread1.Sheets[0].Cells[i, 0].Text = "2012 年";
                this.FpSpread1.Sheets[0].Cells[i, 1].Text = "全国销售统计";

            }

            ////向 spread 报表中写数据
            FarPoint.Web.Spread.SheetView report = this.FpSpread1.Sheets[0];

            if (dt == null)
                return;

            for (int col = 2; col < 5; col++)
            {
                for (int row = 4; row < 9; row++)
                {
                    report.Cells[row, col].Text = dt.Rows[row - 4][col - 2].ToString();
                }
            }

            report.PageSize = 8 + dt.Rows.Count;
        }
        #endregion

        #region 检测数据字段
        private bool CheckYearDataField()
        {
            bool flag = true;
            FarPoint.Web.Spread.SheetView reportinfo = this.FpSpread1.Sheets[0];
            try
            {
                if (reportinfo.Cells[3, 0].Text != "销售时间")
                {
                    return false;
                }
                if (reportinfo.Cells[3, 1].Text != "销售备注")
                {
                    return false;
                }
                if (reportinfo.Cells[3, 2].Text != "品名")
                {
                    return false;
                }
                if (reportinfo.Cells[3, 3].Text != "数量")
                {
                    return false;
                }
            }
            catch (Exception)
            {
                return false;
            }
            return flag;
        }
        #endregion

        private void FontBold()
        {
            int r = FpSpread1.ActiveSheetView.ActiveRow;
            int c = FpSpread1.ActiveSheetView.ActiveColumn;

            FontInfo f = FpSpread1.ActiveSheetView.Cells[r, c].Font;

            if (FpSpread1.ActiveSheetView.SelectionModel.Count == 0)
                f.Bold = !f.Bold;
            else
            {
                FarPoint.Web.Spread.StyleInfo si = new FarPoint.Web.Spread.StyleInfo();
                si.Font.CopyFrom(FpSpread1.ActiveSheetView.Cells[r, c].Font);
                si.Font.Bold = !f.Bold;

                FarPoint.Web.Spread.Model.CellRange cr = FpSpread1.ActiveSheetView.SelectionModel[0];
                FpSpread1.ActiveSheetView.Cells[cr.Row, cr.Column, cr.Row + cr.RowCount - 1, cr.Column + cr.ColumnCount - 1].Font = si.Font;
            }
        }

        private void FontItalic()
        {
            int r = FpSpread1.ActiveSheetView.ActiveRow;
            int c = FpSpread1.ActiveSheetView.ActiveColumn;

            FontInfo f = FpSpread1.ActiveSheetView.Cells[r, c].Font;

            if (FpSpread1.ActiveSheetView.SelectionModel.Count == 0)
                f.Italic = !f.Italic;
            else
            {
                FarPoint.Web.Spread.StyleInfo si = new FarPoint.Web.Spread.StyleInfo();
                si.Font.CopyFrom(FpSpread1.ActiveSheetView.Cells[r, c].Font);
                si.Font.Italic = !f.Italic;

                FarPoint.Web.Spread.Model.CellRange cr = FpSpread1.ActiveSheetView.SelectionModel[0];
                FpSpread1.ActiveSheetView.Cells[cr.Row, cr.Column, cr.Row + cr.RowCount - 1, cr.Column + cr.ColumnCount - 1].Font = si.Font;
            }
        }

        private void FontUnderline()
        {
            int r = FpSpread1.ActiveSheetView.ActiveRow;
            int c = FpSpread1.ActiveSheetView.ActiveColumn;

            FontInfo f = FpSpread1.ActiveSheetView.Cells[r, c].Font;

            if (FpSpread1.ActiveSheetView.SelectionModel.Count == 0)
                f.Underline = !f.Underline;
            else
            {
                FarPoint.Web.Spread.StyleInfo si = new FarPoint.Web.Spread.StyleInfo();
                si.Font.CopyFrom(FpSpread1.ActiveSheetView.Cells[r, c].Font);
                si.Font.Underline = !f.Underline;

                FarPoint.Web.Spread.Model.CellRange cr = FpSpread1.ActiveSheetView.SelectionModel[0];
                FpSpread1.ActiveSheetView.Cells[cr.Row, cr.Column, cr.Row + cr.RowCount - 1, cr.Column + cr.ColumnCount - 1].Font = si.Font;
            }
        }

        private void FontName(string name)
        {
            int r = FpSpread1.ActiveSheetView.ActiveRow;
            int c = FpSpread1.ActiveSheetView.ActiveColumn;

            FontInfo f = FpSpread1.ActiveSheetView.Cells[r, c].Font;
            if (FpSpread1.ActiveSheetView.SelectionModel.Count == 0)
                f.Name = name;
            else
            {
                FarPoint.Web.Spread.StyleInfo si = new FarPoint.Web.Spread.StyleInfo();
                si.Font.CopyFrom(FpSpread1.ActiveSheetView.Cells[r, c].Font);
                si.Font.Name = name;

                FarPoint.Web.Spread.Model.CellRange cr = FpSpread1.ActiveSheetView.SelectionModel[0];
                FpSpread1.ActiveSheetView.Cells[cr.Row, cr.Column, cr.Row + cr.RowCount - 1, cr.Column + cr.ColumnCount - 1].Font = si.Font;
            }
        }

        private void FontSize(int size)
        {
            int r = FpSpread1.ActiveSheetView.ActiveRow;
            int c = FpSpread1.ActiveSheetView.ActiveColumn;

            FarPoint.Web.Spread.StyleInfo si = new FarPoint.Web.Spread.StyleInfo();

            FontInfo f = FpSpread1.ActiveSheetView.StyleModel.GetCompositeInfo(r, c, -1, null).Font;

            int s1 = size;
            switch (s1)
            {
                case 1:
                    f.Size = FontUnit.Smaller;
                    break;
                case 2:
                    f.Size = FontUnit.Small;
                    break;
                case 3:
                    f.Size = FontUnit.Medium;
                    break;
                case 4:
                    f.Size = FontUnit.Larger;
                    break;
                case 5:
                    f.Size = FontUnit.Large;
                    break;
                case 6:
                    f.Size = FontUnit.XLarge;
                    break;
            }

            if (FpSpread1.ActiveSheetView.SelectionModel.Count > 0)
            {
                FarPoint.Web.Spread.Model.CellRange cr = FpSpread1.ActiveSheetView.SelectionModel[0];
                FpSpread1.ActiveSheetView.Cells[cr.Row, cr.Column, cr.Row + cr.RowCount - 1, cr.Column + cr.ColumnCount - 1].Font = f;
            }
        }

        private void BackColor(Color backcolor)
        {
            int r = FpSpread1.ActiveSheetView.ActiveRow;
            int c = FpSpread1.ActiveSheetView.ActiveColumn;

            if (FpSpread1.ActiveSheetView.SelectionModel.Count == 0)
                FpSpread1.ActiveSheetView.Cells[r, c].BackColor = backcolor;
            else
            {
                FarPoint.Web.Spread.Model.CellRange cr = FpSpread1.ActiveSheetView.SelectionModel[0];
                FpSpread1.ActiveSheetView.Cells[cr.Row, cr.Column, cr.Row + cr.RowCount - 1, cr.Column + cr.ColumnCount - 1].BackColor = backcolor;
            }
        }

        private void ForeColor(Color forecolor)
        {
            int r = FpSpread1.ActiveSheetView.ActiveRow;
            int c = FpSpread1.ActiveSheetView.ActiveColumn;

            if (FpSpread1.ActiveSheetView.SelectionModel.Count == 0)
                FpSpread1.ActiveSheetView.Cells[r, c].ForeColor = forecolor;
            else
            {
                FarPoint.Web.Spread.Model.CellRange cr = FpSpread1.ActiveSheetView.SelectionModel[0];
                FpSpread1.ActiveSheetView.Cells[cr.Row, cr.Column, cr.Row + cr.RowCount - 1, cr.Column + cr.ColumnCount - 1].ForeColor = forecolor;
            }
        }

        private void AlignLeft()
        {
            int r = FpSpread1.ActiveSheetView.ActiveRow;
            int c = FpSpread1.ActiveSheetView.ActiveColumn;

            if (FpSpread1.ActiveSheetView.SelectionModel.Count == 0)
                FpSpread1.ActiveSheetView.Cells[r, c].HorizontalAlign = HorizontalAlign.Left;
            else
            {
                FarPoint.Web.Spread.Model.CellRange cr = FpSpread1.ActiveSheetView.SelectionModel[0];
                FpSpread1.ActiveSheetView.Cells[cr.Row, cr.Column, cr.Row + cr.RowCount - 1, cr.Column + cr.ColumnCount - 1].HorizontalAlign = HorizontalAlign.Left;
            }
        }

        private void AlignCenter()
        {
            int r = FpSpread1.ActiveSheetView.ActiveRow;
            int c = FpSpread1.ActiveSheetView.ActiveColumn;

            if (FpSpread1.ActiveSheetView.SelectionModel.Count == 0)
                FpSpread1.ActiveSheetView.Cells[r, c].HorizontalAlign = HorizontalAlign.Center;
            else
            {
                FarPoint.Web.Spread.Model.CellRange cr = FpSpread1.ActiveSheetView.SelectionModel[0];
                FpSpread1.ActiveSheetView.Cells[cr.Row, cr.Column, cr.Row + cr.RowCount - 1, cr.Column + cr.ColumnCount - 1].HorizontalAlign = HorizontalAlign.Center;
            }
        }

        private void AlignRight()
        {
            int r = FpSpread1.ActiveSheetView.ActiveRow;
            int c = FpSpread1.ActiveSheetView.ActiveColumn;

            if (FpSpread1.ActiveSheetView.SelectionModel.Count == 0)
                FpSpread1.ActiveSheetView.Cells[r, c].HorizontalAlign = HorizontalAlign.Right;
            else
            {
                FarPoint.Web.Spread.Model.CellRange cr = FpSpread1.ActiveSheetView.SelectionModel[0];
                FpSpread1.ActiveSheetView.Cells[cr.Row, cr.Column, cr.Row + cr.RowCount - 1, cr.Column + cr.ColumnCount - 1].HorizontalAlign = HorizontalAlign.Right;
            }
        }

        private void DeleteRow()
        {
            OleDbConnection dbConnection = new OleDbConnection(connection);
            if (dbConnection == null)
                return;
            if (dbConnection.State == ConnectionState.Closed)
            {
                dbConnection.Open();
            }
            int row = this.FpSpread1.Sheets[0].ActiveRow;
            int col = this.FpSpread1.Sheets[0].ActiveColumn;

            string id = this.FpSpread1.Sheets[0].Cells[row, 0].Text;

            string delStr = string.Format("delete from SalesData where ID={0}", id);
            OleDbCommand command = new OleDbCommand(delStr, dbConnection);
            command.ExecuteNonQuery();

            dbConnection.Close();
        }

        private void UpdateRow(string parameters)
        {
            string[] datas = parameters.Split('=');

            OleDbConnection dbConnection = new OleDbConnection(connection);
            if (dbConnection == null)
                return;
            if (dbConnection.State == ConnectionState.Closed)
            {
                dbConnection.Open();
            }

            int row = this.FpSpread1.Sheets[0].ActiveRow;
            int col = this.FpSpread1.Sheets[0].ActiveColumn;

            string delStr = string.Format("UPDATE SalesData SET 日期=#{0}#,  产品='{1}',数量='{2}',收入='{3}',区域='{4}',销售经理='{5}',备注='{6}' WHERE ID = {7}",
                datas[2], datas[3], datas[4], datas[5], datas[6], datas[7], datas[8], datas[1]);
            OleDbCommand command = new OleDbCommand(delStr, dbConnection);
            command.ExecuteNonQuery();
            dbConnection.Close();

            this.FpSpread1.Sheets[0].Cells[row, 1].Text = datas[2];
            this.FpSpread1.Sheets[0].Cells[row, 2].Text = datas[3];
            this.FpSpread1.Sheets[0].Cells[row, 3].Text = datas[4];
            this.FpSpread1.Sheets[0].Cells[row, 4].Text = datas[5];
            this.FpSpread1.Sheets[0].Cells[row, 5].Text = datas[6];
            this.FpSpread1.Sheets[0].Cells[row, 6].Text = datas[7];
            this.FpSpread1.Sheets[0].Cells[row, 7].Text = datas[8];
            this.FpSpread1.Sheets[0].Cells[row, 8].Text = "双击单元格编辑";

            this.FpSpread1.Sheets[0].Rows[row].BackColor = System.Drawing.Color.LightBlue;
        }

        protected void FpSpread1_ButtonCommand1(object sender, FarPoint.Web.Spread.SpreadCommandEventArgs e)
        {
            if (e.CommandName.Equals("FontBold"))
                this.FontBold();
            else if (e.CommandName.Equals("FontItalic"))
                this.FontItalic();
            else if (e.CommandName.Equals("FontUnderline"))
                this.FontUnderline();
            else if (e.CommandName.StartsWith("FontName"))
            {
                int n = e.CommandName.IndexOf(".") + 1;
                this.FontName(e.CommandName.Substring(n));
            }
            else if (e.CommandName.StartsWith("FontSize"))
            {
                int n = e.CommandName.IndexOf(".") + 1;
                int size = int.Parse(e.CommandName.Substring(n));
                this.FontSize(size);
            }
            else if (e.CommandName.StartsWith("BackColor"))
            {
                int n = e.CommandName.IndexOf(".") + 1;
                Color bc = Color.FromName(e.CommandName.Substring(n));
                this.BackColor(bc);
            }
            else if (e.CommandName.StartsWith("ForeColor"))
            {
                int n = e.CommandName.IndexOf(".") + 1;
                Color fc = Color.FromName(e.CommandName.Substring(n));
                this.ForeColor(fc);
            }
            else if (e.CommandName.Equals("AlignLeft"))
                this.AlignLeft();
            else if (e.CommandName.Equals("AlignCenter"))
                this.AlignCenter();
            else if (e.CommandName.Equals("AlignRight"))
                this.AlignRight();
            else if (e.CommandName == "DeleteRow")
                this.DeleteRow();
            else if (e.CommandName.Contains("btnupdate"))
            {
                UpdateRow(e.CommandName);
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            if (this.FileUpload1.HasFile)
            {
                string filename = this.FileUpload1.FileName;

                this.FileUpload1.SaveAs(this.Server.MapPath(@"resource\template\" + filename));
                this.FpSpread1.OpenExcel(this.Server.MapPath(@"resource\template\" + filename), FarPoint.Excel.ExcelOpenFlags.TruncateEmptyRowsAndColumns);

                if (Session["report"] == "year")
                {
                    InitYearReport();
                }
                else if (Session["report"] == "daily")
                {
                    InitDailyReport();
                }
                else if (Session["report"] == "list")
                {
                    InitListReport();
                }
            }
        }

        protected override void Render(HtmlTextWriter writer)
        {
            if (Session["report"] == "list")
            {
                Table spreadTable = this.FpSpread1.FindControl("viewport") as Table;
                spreadTable.Attributes.Add("onclick", "del()");
                spreadTable.Attributes.Add("ondblclick", "edit()");
            }
            base.Render(writer);
        }

        protected void FpSpread1_UpdateCommand(object sender, FarPoint.Web.Spread.SpreadCommandEventArgs e)
        {

        }

        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            switch (this.DropDownList1.SelectedValue)
            {
                case "销售明细表":
                    this.FpSpread1.Sheets[0].Reset();
                    //this.FpSpread1.Open(this.Server.MapPath(@"\resource\template\listdata.xml"));
                    InitListReport();
                    Session["report"] = "list";
                    SetSkin();
                    break;

                case "年报表":
                    this.FpSpread1.Open(this.Server.MapPath(@"resource\template\sryear.xml"));
                    InitYearReport();
                    Session["report"] = "year";
                    SetSkin();
                    break;

                case "日报表":
                    this.FpSpread1.Open(this.Server.MapPath(@"resource\template\dailyreport.xml"));
                    InitDailyReport();
                    Session["report"] = "daily";
                    SetSkin();
                    break;
                default:
                    break;
            }
        }

        private void SetSkin()
        {
            if (Session["skin"] != null)
            {
                int skin = (int)(Session["skin"]);
                FarPoint.Web.Spread.DefaultSkins.GetAt(skin).Apply(FpSpread1.Sheets[0]);
                this.FpSpread1.Sheets[0].GridLines = GridLines.None;
            }
        }
    }
}